﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace CRUDGenerator.Sql
{
    /// <summary>
    /// Get database schema
    /// </summary>
    public class Discover
    {
        private readonly string _connectionString;

        /// <summary>
        /// Initializes a new instance of the <see cref="Discover" /> class.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        public Discover(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// Gets the tables.
        /// </summary>
        public IEnumerable<string> GetTables()
        {
            var result = new List<string>();
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                var server = new Server(new ServerConnection(sqlConnection));
                var database = new Database(server, sqlConnection.Database);
                database.Refresh();
                foreach (Table table in database.Tables)
                {
                    table.Refresh();
                    result.Add(table.Name);
                }
            }
            return result;
        }

        /// <summary>
        /// Gets the columns.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        public IEnumerable<ColumnInformation> GetColumns(string tableName)
        {
            var result = new List<ColumnInformation>();
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                var server = new Server(new ServerConnection(sqlConnection));
                var database = new Database(server, sqlConnection.Database);
                database.Refresh();
                var table = new Table(database, tableName);
                table.Refresh();
                foreach (Column column in table.Columns)
                {
                    var value = new ColumnInformation
                        {
                            Name = column.Name,
                            DataTypeName = column.DataType.Name,
                            IsIdentity = column.Identity,
                            IsPrimaryKey = column.InPrimaryKey
                        };
                    if (value.DataTypeName == "nvarchar")
                    {
                        value.DataTypeName = value.DataTypeName + "(" + column.DataType.MaximumLength + ")";
                    }
                    result.Add(value);
                }
            }
            return result;
        }

        /// <summary>
        /// Generates the insert procedure.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="procedureName">Name of the procedure.</param>
        public string GenerateInsertProcedure(string table, string procedureName)
        {
            List<ColumnInformation> columns = GetColumns(table).ToList();

            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + procedureName + "]\n");

            result.Append(string.Join(
                         ",\n",
                columns.Where(x => !x.IsIdentity).Select(
                    x => StringBuilderExtension.Tab + "@" + FirstLetterLowerCase(x.Name) + " " + x.DataTypeName)));

            result.Append("\nAS\nINSERT INTO [dbo].[" + table + "]\n(");

            result.Append(string.Join(",\n", columns.Where(x => !x.IsIdentity).Select(x => StringBuilderExtension.Tab + x.Name)));

            result.Append(")\nVALUES(\n");

            result.Append(
                string.Join(
                    ",\n",
                    columns.Where(x => !x.IsIdentity).Select(
                        x => StringBuilderExtension.Tab + "@" + FirstLetterLowerCase(x.Name))));

            result.Append(")\n");

            ColumnInformation indentiry = columns.FirstOrDefault(x => x.IsIdentity);

            if (indentiry != null)
            {
                result.Append("declare @" + FirstLetterLowerCase(indentiry.Name) + " " + indentiry.DataTypeName
                         + " = " + "@@IDENTITY\n");
            }

            result.Append(GenerateSelectByPrimaryKey(table));

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the delete procedure.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="procedureName">Name of the procedure.</param>
        public string GenerateDeleteProcedure(string table, string procedureName)
        {
            List<ColumnInformation> columns = GetColumns(table).ToList();
            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + procedureName + "]\n");
            ColumnInformation indentity = columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("@" + FirstLetterLowerCase(indentity.Name) + " " + indentity.DataTypeName + "\n");
            }
            else
            {
                List<ColumnInformation> primaries = columns.Where(x => x.IsPrimaryKey).ToList();
                int i = 0;
                foreach (ColumnInformation primary in primaries)
                {
                    result.Append(StringBuilderExtension.Tab+"@" + FirstLetterLowerCase(primary.Name) + " " + primary.DataTypeName);
                    if (i < primaries.Count - 1)
                    {
                        result.Append(",");
                    }
                    result.Append("\n");
                    i++;
                }
            }

            result.Append("\nAS\nDELETE FROM [dbo].[" + table + "]\n");
            indentity = columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("\nWHERE\n");

                result.Append(StringBuilderExtension.Tab + table + ".[" + indentity.Name + "]=@" + FirstLetterLowerCase(indentity.Name));
            }
            else
            {
                List<ColumnInformation> primaries = columns.Where(x => x.IsPrimaryKey).ToList();
                result.Append("\nWHERE\n");
                int i = 0;
                foreach (ColumnInformation primary in primaries)
                {
                    result.Append(StringBuilderExtension.Tab+ "(" + table + ".[" + primary.Name + "]=@" + FirstLetterLowerCase(primary.Name)
                             + " )");
                    if (i < primaries.Count - 1)
                    {
                        result.Append(" AND\n");
                    }
                    i++;
                }
            }

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the update procedure.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="procedureName">Name of the procedure.</param>
        public string GenerateUpdateProcedure(string table, string procedureName)
        {
            List<ColumnInformation> columns = GetColumns(table).ToList();

            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + procedureName + "]\n");

            result.Append(string.Join(
                         ",\n", columns.Select(x => StringBuilderExtension.Tab + "@" + FirstLetterLowerCase(x.Name) + " " + x.DataTypeName)));

            result.Append("\nAS\nUPDATE [dbo].[" + table + "] SET ");

            result.Append(
                     string.Join(
                         ",\n",
                         columns.Where(x => !x.IsIdentity).Select(
                             x => "\t[dbo].[" + table + "].[" + x.Name + "] = @" + FirstLetterLowerCase(x.Name))));

            result.Append("\n");

            ColumnInformation indentity = columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("WHERE\n");

                result.Append(StringBuilderExtension.Tab+"[dbo].[" + table + "].[" + indentity.Name + "]=@"+ FirstLetterLowerCase(indentity.Name));
            }
            else
            {
                List<ColumnInformation> primaries = columns.Where(x => x.IsPrimaryKey).ToList();
                result.Append("WHERE\n");
                int i = 0;
                foreach (ColumnInformation primary in primaries)
                {
                    result.Append(
                        StringBuilderExtension.Tab + "(" + table + ".[" + primary.Name + "]=@"
                        + FirstLetterLowerCase(primary.Name) + " )");
                    if (i < primaries.Count - 1)
                    {
                        result.Append(" AND\n");
                    }
                    i++;
                }
            }

            result.Append(GenerateSelectByPrimaryKey(table));

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the select all procedure.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="procedureName">Name of the procedure.</param>
        public string GenerateSelectAllProcedure(string table, string procedureName)
        {
            List<ColumnInformation> columns = GetColumns(table).ToList();

            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + procedureName + "]\n");
            result.Append("\nAS\nSELECT ");

            result.Append(string.Join(",\n", columns.Select(x => StringBuilderExtension.Tab + "[dbo].[" + table + "].[" + x.Name + "]")));

            result.Append("\nFROM " + table + " \n");

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Generates the select by id procedure.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="procedureName">Name of the procedure.</param>
        public string GenerateSelectByIdProcedure(string table, string procedureName)
        {
            List<ColumnInformation> columns = GetColumns(table).ToList();

            var result = new StringBuilder("CREATE PROCEDURE [dbo].[" + procedureName + "]\n");

            result.Append(string.Join(
                         ",\n",
                         columns.Where(x => x.IsIdentity || x.IsPrimaryKey).Select(
                             x => StringBuilderExtension.Tab + "@" + FirstLetterLowerCase(x.Name) + " " + x.DataTypeName)));

            result.Append("\nAS ");

            result.Append(GenerateSelectByPrimaryKey(table));

            return result.Append("\nRETURN 0\n").ToString();
        }

        /// <summary>
        /// Firsts the letter lower case.
        /// </summary>
        /// <param name="text">The text.</param>
        private string FirstLetterLowerCase(string text)
        {
            return string.Format("{0}{1}", text[0].ToString().ToLower(), text.Substring(1, text.Length - 1));
        }

        /// <summary>
        /// Generates the select by primary key.
        /// </summary>
        /// <param name="table">The table.</param>
        private string GenerateSelectByPrimaryKey(string table)
        {
            List<ColumnInformation> columns = GetColumns(table).ToList();

            var result = new StringBuilder("\nSELECT\n");

            result.Append(string.Join(",\n", columns.Select(x => StringBuilderExtension.Tab + "[" + x.Name + "]")));

            result.Append("\nFROM [dbo].[" + table + "] \n");

            ColumnInformation indentity = columns.FirstOrDefault(x => x.IsIdentity);

            if (indentity != null)
            {
                result.Append("WHERE\n");

                result.Append(StringBuilderExtension.Tab+ table + ".[" + indentity.Name + "]=@" + FirstLetterLowerCase(indentity.Name));
            }
            else
            {
                List<ColumnInformation> primaries = columns.Where(x => x.IsPrimaryKey).ToList();
                result.Append("WHERE\n");
                int i = 0;
                foreach (ColumnInformation primary in primaries)
                {
                    result.Append(StringBuilderExtension.Tab+"(" + table + ".[" + primary.Name + "]=@" + FirstLetterLowerCase(primary.Name)
                             + " )");
                    if (i < primaries.Count - 1)
                    {
                        result.Append(" AND\n");
                    }
                    i++;
                }
            }
            return result.ToString();
        }
    }
}